﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Security.Cryptography;

using edowork.com.SQL2005;

namespace WebInvoicingsys.system.settle
{
    public partial class settleclose_add : System.Web.UI.Page
    {
        //前台输出提示信息变量
        public string alert_msg = "";
        
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ;
            }
        }

        protected void btnSettleclose_Click(object sender, EventArgs e)
        {
            alert_msg = "";

            string years = uc_from_years.getValue();
            string months = uc_from_months.getValue();
            string remark = txt_remark.Text.Trim();

            if (String.IsNullOrEmpty(years))
            {
                alert_msg = String.Format(Scripts.alert_warning, "请选择所在年份");

                Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                return;
            }

            if (String.IsNullOrEmpty(months))
            {
                alert_msg = String.Format(Scripts.alert_warning, "请选择月结月份");

                Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                return;
            }

            //系统月结编码
            string sysmid = years + months;
            string closedate = DateTime.Now.ToString("yyyy-MM-dd");

            //保存数据入数据库
            using (edowork.com.SQL2005.TranUtil tranutil = new TranUtil(Session["connectionString"].ToString()))
            {
                System.Data.SqlClient.SqlTransaction tran = null;

                try
                {
                    tran = tranutil.GetSqlTransaction();
                }
                catch (Exception er)
                {
                    alert_msg = String.Format(Scripts.alert_warning, er.Message);

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                edowork.com.SQL2005.Execute exec = new Execute(Session["connectionString"].ToString());

                string sql = "", error_msg = "";
                int result = -1;
                DataSet dsResult = null;

                #region 锁定所有需操作的表资源

                sql = "exec dbo.sys_settleclose_locktable";

                dsResult = exec.ExecStoreProcQueryDataSet(sql, ref error_msg, tran);

                if (dsResult == null)
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, error_msg);

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }


                #endregion

                #region 检查是否存在当前选择的月结时间

                sql = String.Format("select sysmid from sys_monthly_main where years='{0}' and months='{1}'", years, months);

                dsResult = exec.ExecSQLQueryDataSet(sql, ref error_msg, tran);

                if ((dsResult == null) || (dsResult.Tables[0].Rows.Count > 0))
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, "已存在相同的月结月份。");

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                #endregion

                #region 检查是否存在夸月月结的情况

                /*
                 * 如：月结了2月份，但3月份没月结时则执行4月份的月结
                 * 
                 * */
                sql = "select max(sysmid) sysmid, years, months, sysmid from sys_monthly_main group by years, months, sysmid";

                dsResult = exec.ExecSQLQueryDataSet(sql, ref error_msg, tran);

                if (dsResult == null)
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, error_msg);

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                bool check = false;

                foreach (DataRow dr in dsResult.Tables[0].Rows)
                {
                    //检查是否存在夸月月结的情况
                    //取得当前月结日期，然后将月份增加1，如果月份增加1后等于当前选择，则说明合法
                    try
                    {
                        string closedate_check = DateTime.Parse(dr["years"].ToString() + "-" + dr["months"].ToString() + "-01").AddMonths(1).ToString("yyyyMM");

                        if (closedate_check.Equals(years + months))
                        {
                            check = true;
                        }
                    }
                    catch(Exception er)
                    {
                        tran.Rollback();
                        tranutil.CloseConnection();

                        alert_msg = String.Format(Scripts.alert_warning, er.Message);

                        Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                        return;
                    }
                    
                }

                if (check == false)
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, "跨月月结，请注意。");

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                #endregion

                #region 检查是否存在没完成的单据

                sql = @"select ljid as id from Library_adj where checkstu='0'
                        union all
                        select csid as id from check_stock where checkstu='0'
                        union all
                        select billsno as id from bills where checkstu='0'
                        union all
                        select pcsid as id from purchase where checkstu='0'
                        union all
                        select sabid as id from sellbill where checkstu='0'";

                dsResult = exec.ExecSQLQueryDataSet(sql, ref error_msg, tran);

                if ((dsResult == null) || (dsResult.Tables[0].Rows.Count > 0))
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, "存在没完成审核的单据。");

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                #endregion

                #region 更新所有单据的月结状态

                string s_date = DateTime.Parse(years + "-" + months + "-01").ToString("yyyy-MM-dd");
                string e_date = DateTime.Parse(years + "-" + months + "-01").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

                sql = String.Format("exec dbo.sys_SettleClose_updatetable '{0}', '{1}', '{2}', '{3}', '{4}', '{5}'", sysmid, closedate, s_date, e_date, years, months);

                dsResult = exec.ExecStoreProcQueryDataSet(sql, ref error_msg, tran);

                if (dsResult == null)
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, error_msg);

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                #endregion

                //取得当期库存数
                sql = "select *, 0 qty2 into #tm_Settle_NowQty from inventory";

                result = exec.ExecSQLQueryInt(sql, ref error_msg, tran);

                if (result < 0)
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, error_msg);

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                //取得上期数
                sql = @"update #tm_Settle_NowQty
                          set qty2 = b.qty1
                          from #tm_Settle_NowQty a,
                               (select * from sys_monthly_stock where sysmid in (select max(sysmid) sysmid from sys_monthly_main)) b
                           where a.warehouseid=b.warehouseid and a.areaid=b.areaid and a.storageid=b.storageid and a.productcode=b.productcode";

                result = exec.ExecSQLQueryInt(sql, ref error_msg, tran);

                if (result < 0)
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, error_msg);

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                //写入月结信息
                sql = "insert into sys_monthly_main(sysmid, years, months, sysdate, remark) values('{0}', '{1}', '{2}', '{3}', '{4}')";
                sql = String.Format(sql, sysmid, years, months, closedate, remark);

                result = exec.ExecSQLQueryInt(sql, ref error_msg, tran);

                if (result <= 0)
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, error_msg);

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                //写入月结库存信息
                sql = @"insert into sys_monthly_stock(sysmid, warehouseid, areaid, storageid, productcode, qty1, qty2)
                          select '{0}', warehouseid, areaid, storageid, productcode, qty1, qty2
                            from #tm_Settle_NowQty";
                
                sql = String.Format(sql, sysmid);

                result = exec.ExecSQLQueryInt(sql, ref error_msg, tran);

                if (result <= 0)
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, error_msg);

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                //写入月结单据信息到临时表
                sql = @"select a.* into #tmp_settle_billtype
                            from
                            (select 'PUS' as billtype, count(pspid) as qty1 from purchaseplan where m_id='{0}' and m_stu='1'
                            union all
                            select 'SPS' as billtype, count(sepid) as qty1 from saleplan where m_id='{0}' and m_stu='1'
                            union all
                            select 'PU' as billtype, count(pcsid) as qty1 from purchase where m_id='{0}' and m_stu='1'
                            union all
                            select 'SP' as billtype, count(sabid) as qty1 from sellbill where m_id='{0}' and m_stu='1'
                            union all
                            select billtype, count(billsno) as qty1 from bills where m_id='{0}' and m_stu='1' group by billtype
                            union all
                            select billtype, count(csid) as qty1 from check_stock where m_id='{0}' and m_stu='1' group by billtype
                            union all
                            select 'LD' as billtype, count(ljid) as qty1 from Library_adj where m_id='{0}' and m_stu='1') as a";

                sql = String.Format(sql, sysmid);

                result = exec.ExecSQLQueryInt(sql, ref error_msg, tran);

                if (result < 0)
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, error_msg);

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                //写入月结单据信息
                sql = @"insert into sys_monthly_bills(sysmid, billtype, qty1)
                          select '{0}', billtype, qty1
                            from #tmp_settle_billtype";

                sql = String.Format(sql, sysmid);

                result = exec.ExecSQLQueryInt(sql, ref error_msg, tran);

                if (result <= 0)
                {
                    tran.Rollback();
                    tranutil.CloseConnection();

                    alert_msg = String.Format(Scripts.alert_warning, error_msg);

                    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);

                    return;
                }

                tran.Commit();
                tranutil.CloseConnection();
            }

            alert_msg = String.Format(Scripts.alert_success, Errormsg.settle_success_Save);

            Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", Scripts.alert_script, true);
        }

        protected void btnReturn_Click(object sender, EventArgs e)
        {
            string url = "/system/settle/settleclose.aspx";

            Response.Redirect(url);
        }
    }
}